-- @author lixiyong01
-- @date 2023.03.14
-- 健康分详情表 存储/任务
-- 【已下线，0hot仅仅是特性，在时间周期设置上体现即可】

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_rock_health_score partition (dt=${dateSuffix}, tag='store', code='table_0hot')
select
    nvl(a.db_name,'-') as project,
    nvl(b.org_cn_name,'-') as team,
    nvl(a.user_name,'-') as owner_id,
    nvl(a.table_id,0) as app_id,
    concat(a.db_name,'.',a.tbl_name) as app_name,
    case
        -- 表已经做了极限存储或人工审核过，也直接100分
        --when a.is_managed then '100,表已经做极限存储或人工审核过'

        -- 表有下游依赖，给100分
        when a.hot = 1 then '100,表有下游依赖'

        -- 产出时间距现在还不足10天的表，还未积累足够的访问情况信息，无法合理配置生命周期，会先给99分
        when a.produce_time > '${#date(0, 0, -10):yyyy-MM-dd#}' then '099,新表不足10天'

        -- 表为空表，需要进行下线处理，给90分
        when a.filesize=0 then '090,表为空表'

        -- 表无下游依赖，给80分
        when a.hot = 0 then '080,表无下游依赖'

        -- 未出发管理规则，直接给100分
        else '100,未触发管理规则'
    end as health_score,
    concat_ws(',',
        'hot', cast(nvl(a.hot,0) as string),
        'produce_time', nvl(a.produce_time,'-'),
        'filesize', cast(nvl(a.filesize,0) as string)
    ) as data_content,
    nvl(a.filesize,1) as weight
from (
    select
        table_id,
        db_name,
        tbl_name,
        tbl_type,
        user_name,
        location,
        is_partition,
        dt_partition,
        lifecycle,
        hot,
        filesize,
        is_claim,
        security_level,
        importance_level,
        access_last_time,
        update_last_time,
        status,
        create_time,
        produce_time,
        case
            when max_access_span_in_100_days is NULL then 0
            else max_access_span_in_100_days
        end as max_access_span_in_100_days,
        case
            when max_access_span_in_100_days <= 4 or max_access_span_in_100_days is NULL then 7
            when max_access_span_in_100_days <= 12 then 15
            when max_access_span_in_100_days <= 30 then 33
            when max_access_span_in_100_days <= 90 then 93
            when max_access_span_in_100_days <= 180 then 183
            when max_access_span_in_100_days <= 365 then 368
            when max_access_span_in_100_days <= 731 then 1095
            else -1
        end as recommend_lifecycle
    from hdp_lbg_supin_zplisting.dwd_zp_rock_table_meta_info
    where dt = '${#date(0, 0, -1):yyyyMMdd#}' and filesize > 2*1024*1024*1024
) a
left join (
    select
        username,
        realname as user_name_cn,
        concat(
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-4],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-3],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-2],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-1]
        ) as org_cn_name
    from hdp_lbg_supin_zplisting.dim_zp_n_n_emp_org
    where dt = '${#date(0, 0, -1):yyyyMMdd#}'
) b on a.user_name = b.username;